TD 4 : SQL

Aggrégations, Partitions, Fenêtres

world

SQL

Aggregation

Fenêtres

Published

October 17, 2025

L3 MIASHS

Université Paris Cité

Année 2025

Course Homepage

Moodle

Documentation Postgres

Documentation Postgres en Français

Fonctions d’agrégation

Les fonctions d’agrégation permettent d’effectuer des opérations avancées sur les solutions d’une requête (sur une table) comme : compter les lignes, sélectionner le maximum dans une colonne, etc.

Une des opérations les plus courantes est de compter. COUNT(col) permet de compter les résultats d’une requête.

Count

Pour compter les pays en Europe, on écrira :

SELECT COUNT(countrycode)
FROM world.country
WHERE continent='Europe';

Cette requête renvoie une table ayant une ligne et une colonne contenant le nombre de lignes dans le résultat de la requête.

GROUP BY

Admettons qu’on veuille compter les pays par continent. On doit alors utiliser la clause GROUP BY :

SELECT 
  continent, COUNT(countrycode)
FROM 
  world.country
GROUP BY 
  continent;

Cette requête regroupe les lignes de la table country par valeur de la colonne continent et pour chaque groupe, compte le nombre de countrycode y apparaissant. Lorsque plusieurs lignes sont susceptibles d’avoir la même valeur, on peut compter seulement le nombre d’occurences distinctes avec COUNT(DISTINCT col).

Question

Écrire une requête qui compte le nombre de langues parlées dans chaque pays.

Question

Écrire une requête qui compte le nombre de langues parlées dans le monde.

Question

Écrire une requête qui compte le nombre de langues officielles par pays.

Une solution presque bonne :

Cependant, on perd les pays qui ne possèdent pas de langue officielle. On va utiliser une jointure extérieure pour les conserver:

Sum, Max, Min, Avg

Une autre fonction importante est la fonction SUM(col) qui effectue la somme des valeurs (numériques) d’une colonne :

SELECT SUM(population_country)
FROM world.country;

renvoie la population mondiale.

On peut de même utiliser GROUP BY pour faire des paquets :

SELECT 
  continent, SUM(population_country)
FROM 
  world.country
GROUP BY continent;

renvoie la population de chaque continent.

On peut même faire des opérations sur la colonne à l’intérieur de SUM. Par exemple: SUM(percentage/100).

Question

Écrire une requête qui renvoie le nombre de langues officielles par pays

Requêtes (I)

Question

Écrire une requête qui renvoie la surface de chaque région.

Question

Écrire une requête qui compte le nombre de francophones dans le monde.

On peut utiliser de la même façon la fonction MIN (resp. MAX) qui renvoie la plus petite (resp. grande) valeur ou AVG qui renvoie la moyenne.

Question

Combien de personnes vivent dans une capitale européenne ?

Question

Quelle est la capitale européenne la moins peuplée ?

Question

Quelle est la langue la plus parlée dans le monde ?

Having

Parfois, on veut filtrer les requêtes en fonction du résultat d’une fonction d’agrégation.

Par exemple, pour connaître les langues officielles dans plus de 10 pays, on serait tenté d’écrire :

SELECT 
  language 
FROM 
  world.countrylanguage
WHERE 
  COUNT(countrycode) > 10 AND isofficial 
GROUP BY language;

Cela ne fonctionne pas. WHERE applique une condition sur chaque ligne de la table pour les filtrer, par exemple, garder seulement les langues officielles. Ici, on veut ensuite sélectionner les lignes après avoir regroupé par langue et compté.

On utilisera alors HAVING, après la clause GROUP BY:

SELECT 
5  language
FROM 
1  world.countrylanguage
WHERE 
2  isofficial
3GROUP BY language
HAVING 
4  COUNT(countrycode) > 10;

1: La requête concerne la table world.countrylanguage,
2: On filtre les lignes qui correspondent à des langues officielles,
3: On groupe/partitionne la table filtrée selon la langue,
4: On ne garde que les groupes comportant au moins 10 tuples,
5: On projette le résultat sur la colonne language.

Requêtes (II)

Question

Écrire une requête qui renvoie le nombre de pays par régime.

Question

Écrire une requête calculant le nombre de personnes vivant dans des villes de plus d’un million d’habitants.

Question

Écrire une requête qui calcule le nombre total de personnes dans le monde qui n’habitent pas une ville listée dans la table city.

Réponse : (4,649,189,566)

Question

Écrire une requête qui compte le nombre moyen de langues parlées par pays dans chaque région.

Question

Écrire une requête qui donne la liste des pays ayant deux langues officielles parlées chacune par plus du quart de la population.

Pas besoin d’agrégation à cet endroit là.

Question

Écrire une fonction plus_peuplee(p_countrycode text) qui, étant donné le code d’un pays, renvoie le nom de la ville la plus peuplée de ce pays. (schéma : world)

Question

Écrire une fonction langues_region(p_continent text) qui étant donné le nom d’un continent, renvoie le nombre moyen de langues parlées par pays dans chaque région (schéma : world). L’entête de cette fonction doit être :

FUNCTION langues_region(p_continent TEXT) 
RETURNS TABLE(region TEXT, nbmoy NUMERIC)

Question

Écrire une vue qui contient une ligne pour chaque pays où on parle français, présente les pays par population croissante, et contient trois colonnes :

name_country (même type que dans world.country) ;
cumul_loc (de type float4) qui donne le nombre cumulé de locuteurs du français dans les pays où on parle français, moins ou autant peuplés que le pays courant ;
cum_pop (de type float4) qui donne la population cumulée des pays où on parle français, moins ou autant peuplés que le pays courant.

Utilisez une fonction fenêtre (WINDOW) sans partition.

Pour trouver les pays où on parle français, utilisez l’expression language like '%French%'. Vous remarquerez que dans certains pays, il existe plusieurs variétés de ‘French’. Veillez à compter tous les locuteurs, et à ne compter les habitants qu’une seule fois.